Re: Database replication method - Mailing list pgsql-novice

Quick hint: Please do not start a new thread in a mailing list by replying
to an old one and changing the title.  A good email client can tell.  ;)

--As of May 2, 2014 4:01:33 PM +0300, Yuksel Pinarbasi is alleged to have
said:

>
> Hi all,
> I'm new to PostgreSQL and generally to database engines
> (background from MS Access but I started to learn PostgreSQL).
>
> My question is about database architecture.
> There is a small private school with 2 branches in 2 separate locations.
> Their database will manage student and teacher informations (contact
> informations, payments etc.)
> Since a student or a teacher can go to both these two locations, database
> must be shared.
> But, there might be failures to internet connections from time to time
> and I wonder how can we maintain data integrity after connection
> established.
>
> Syncronization speed is not crucial. Mostly their data stays internal . I
> want (ideally):
> 2 Mirrored databases in 2 location, after an update they try to
> synchronise, if a connection occurs try later.
>
> How can I do this with PostgreSQL?

First question: Does the database need to be local to both locations?
Could you use a front-end that connects to the same backend database from
both campuses?  (Like a web front end?)  That's your easiest solution.

Next question is *if* that's not acceptable, do both locations need to be
able to modify the database?  Or can one be the master and the other just a
replication?  If both locations need to be able to modify the data, you are
going to have to have some way to deal with conflicts that arise.

Those questions will help you define your options.  There's a section of
the handbook then dealing with different solutions:
<http://www.postgresql.org/docs/9.3/static/high-availability.html>

Going with your above description, and assuming there isn't a way to
simplify your requirements, the page above points to the Bucardo project as
a solution you'll want to look at:
<http://bucardo.org/wiki/Bucardo>

Daniel T. Staal

---------------------------------------------------------------
This email copyright the author.  Unless otherwise noted, you
are expressly allowed to retransmit, quote, or otherwise use
the contents for non-commercial purposes.  This copyright will
expire 5 years after the author's death, or in 30 years,
whichever is longer, unless such a period is in excess of
local copyright law.
---------------------------------------------------------------